﻿-- =============================================
-- My SQL - результаты выгруженных данных из DB03 TradeX 
-- по кассам - реализация. . .
-- =============================================
select 
	  s.shop_id
	, p.alias
	, (
		SELECT 
			Day 
		FROM 
			VU_VU_t_sale_proceeds_GROUP_Day 
		WHERE
				(shop_id=s.shop_id)
			and	(YEAR="2011")
			and (MONTH="09")) as Day
	, SUM(s.sum) as Sum 
FROM 
	v_sale_proceeds s 
		INNER JOIN point p 
			ON (s.shop_id=p.point_id) AND (p.permit=1) 
WHERE 
		s.tdate >= '2011-09-01' 
	and s.tdate < '2011-09-16' 
GROUP BY s.shop_id, p.point_name 
ORDER BY p.alias 

-- =============================================
-- Процесс загрузки данных из DB03 TradeX
-- =============================================
DELETE FROM t_sale_proceeds WHERE tdate>='2011-09-01' and tdate<'2011-09-16'
DELETE FROM t_otdel_sale_scheme WHERE year="2011"  and month="09"

select
	  convert(char(10),h.TDate,121) as TDate
	, h.ID_Objects
	, m.Department
	, CAST( SUM(s.Quantity*s.RetailPrice-s.Discount) as NUMERIC(15,2) ) as Total
	, CAST( SUM(t.PriceExp*s.Quantity) as NUMERIC(15,2) ) as Seb 
from T_Headers h 
	inner join T_Sales s ON (s.ID_Headers=h.ID) 
	inner join T_Transactions t ON (t.ID_Headers=s.ID_Delivery) and (t.ID_PLU=s.ID_PLU) 
	INNER JOIN T_PLU p ON (p.ID=s.ID_PLU) 
	INNER JOIN T_Models m ON (m.ID=p.ID_Models) 
where 
		(h.ID_Moves=3)
	and	(h.TDate>='2011-09-01') 
	and	(h.TDate<'2011-09-16') 
GROUP BY m.Department, h.TDate, h.ID_Objects 


-- =============================================
-- Процесс загрузки данных из DB_Ex
-- Последний месяц - реализация
-- =============================================
SELECT NAME AS Магазин, SUM(Total) AS Сумма
FROM dbo.vw_Axapta_TradeSale
WHERE (TRANSDATE >= '2011-01-09')
GROUP BY NAME
ORDER BY Магазин
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
SELECT [Год], [Месяц], [Магазин], [Сумма]
FROM [DB_Ex].[dbo].[vw_Axapta_TradeSale_Sum_Month]


